package model;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Work extends BaseModel {
	public enum WorkCategory {
		Nenhuma, Acadêmico, Científico, Matemático, Geográfico
	}

	private long id;
	private long idAuthor;
	private String authorName;
	private String title;
	private String theme;
	private WorkCategory category;
	private List<String> keyWords;
	private String description;
	private File file;
	private Grade grade;
	private String absoluteFilePath;
	private long numberOfDownloads;
	private WorkSharedDB db;

	public Work() throws Exception {
		db = new WorkSharedDB();
	}

	public Work(long idAuthor, String title, String theme, WorkCategory category, List<String> keyWords,
			String description, byte[] workFile, String fileName) throws Exception {
		super();
		this.idAuthor = idAuthor;
		setTitle(title);
		setTheme(theme);
		setCategory(category);
		setKeyWords(keyWords);
		setDescription(description);
		this.file = new File(this.idAuthor, fileName, workFile);
		this.db = new WorkSharedDB();
	}

	public Work(long id) throws Exception {
		setId(id);
		db = new WorkSharedDB();
		fetchAllData(id);
	}

	private void fetchAllData(long id) throws Exception {
		/* @formatter:off */
		String workQuery = "SELECT w.id, w.title, w.theme, w.category, w.downloads, w.description, w.file_path, a.first_name, a.last_name "
		+ " FROM works w INNER JOIN authors a ON (w.id_author = a.id) "
		+ " WHERE w.id = ? ";
		/* @formatter:on */
		db.statement = WorkSharedDB.connection.prepareStatement(workQuery);
		try {
			db.statement.setLong(1, id);
			ResultSet rs = db.statement.executeQuery();
			if (rs.next()) {
				this.setId(id);
				this.setAbsoluteFilePath(Configuration.FILES_LOCATION + rs.getString("w.file_path"));
				this.setTitle(rs.getString("w.title"));
				this.setTheme(rs.getString("w.theme"));
				this.setCategory(WorkCategory.valueOf(rs.getString("w.category")));
				this.setDescription(rs.getString("w.description"));
				this.setNumberOfDownloads(rs.getLong("w.downloads"));
				this.setAuthorName(rs.getString("a.first_name") + " " + rs.getString("a.last_name"));
			}

			// setGrade(fetchGrade(id)); TODO colocar depois
			setKeyWords(fetchKeyWords(id));
		} catch (SQLException e) {
			throw new Exception("Erro ao carregar Trabalho: " + e.getMessage());
		}

	}

	private List<String> fetchKeyWords(long id) throws Exception {
		/* @formatter:off */
		String keyWordsQuery = ""
		+ " SELECT kw.key_word "
		+ " FROM key_words kw "
		+ " WHERE kw.id_work = ? ";
		/* @formatter:on */
		List<String> keyWords = new ArrayList<String>();
		try {
			db.statement = WorkSharedDB.connection.prepareStatement(keyWordsQuery);
			db.statement.setLong(1, id);
			ResultSet rs = db.statement.executeQuery();
			while (rs.next()) {
				keyWords.add(rs.getString("kw.key_word"));
			}
		} catch (SQLException e) {
			throw new Exception("Erro ao carregar palavras-chave: " + e.getMessage());
		}

		return keyWords;
	}

	// TODO continuar o método, escolhi tirar a nota caso a entrega seja segunda
	// private Grade fetchGrade(long id) {
//		/* @formatter:off */
//		String gradeQuery = ""
//		+ " SELECT COUNT(*), SUM(r.evaluation) "
//		+ " FROM works INNER JOIN rates r ON (w.id = r.id_work) "
//		+ " WHERE w.id = ? ";
//		/* @formatter:on */
	//
	// return null;
	// }

	public long getAmountOfRates() {
		return grade.getAmountOfRates();
	}

	public float getAverageGrade() {
		return grade.getAverageGrade();
	}

	public WorkCategory getCategory() {
		return category;
	}

	public String getDescription() {
		return description;
	}

	public File getFile() {
		return file;
	}

	public Grade getGrade() {
		return grade;
	}

	public long getId() {
		return id;
	}

	public List<String> getKeyWords() {
		return keyWords;
	}

	public long getNumberOfDownloads() {
		return numberOfDownloads;
	}

	public String getTheme() {
		return theme;
	}

	public String getTitle() {
		return title;
	}

	public FileInputStream prepareDownload() throws Exception {
		try {
			if (getAbsoluteFilePath() != null) {
				return new FileInputStream(getAbsoluteFilePath());
			} else {
				return null;
			}
		} catch (FileNotFoundException e) {
			throw new Exception("Erro ao procurar arquivo: " + e.getMessage());
		}

		// TODO adicionar no banco um download através de chamar uma prodecure.
		// (se possível no
		// final do download)
	}

	public void rate(int grade) {
		// TODO chamar a procedure que adicionará a avaliação
	}

	public void save() throws Exception {
		// TODO caso tenha o mesmo titulo, e mesmo nome de arquivo, fazer update
		String filePath = "";
		try {
			filePath = file.save();
			db.statement = WorkSharedDB.connection
					.prepareStatement("INSERT INTO works(id_author, title, theme, description, category, file_path) VALUES(?, ?, ?, ?, ?, ?)");
			db.statement.setLong(1, idAuthor);
			db.statement.setString(2, getTitle());
			db.statement.setString(3, getTheme());
			db.statement.setString(4, getDescription());
			db.statement.setString(5, getCategory().toString());
			db.statement.setString(6, filePath);

			db.statement.execute();
			if (keyWords.size() > 0) {
				saveKeyWords();
			}
		} catch (SQLException sqlEx) {
			File.delete(filePath);
			throw new Exception("Erro ao salvar trabalho: " + sqlEx.getMessage());
		}
	}

	private void saveKeyWords() throws SQLException {
		db.statement = WorkSharedDB.connection
				.prepareStatement("Select * from works where title = ? and file_path = ?");
		db.statement.setString(1, getTitle());
		db.statement.setString(2, file.getPath());

		ResultSet rs = db.statement.executeQuery();
		if (rs.next()) {
			this.setId(rs.getLong("id"));
		} else {
			throw new SQLException("Trabalho provavelmente ainda não persistido! title: " + getTitle());
		}

		String insertKeys = "Insert into key_words(id_work, key_word) values(?, ?)";
		for (int i = 0; i < keyWords.size() - 1; i++) {
			insertKeys += ",(?, ?)";
		}
		db.statement = WorkSharedDB.connection.prepareStatement(insertKeys);
		final int numParameters = 2;
		for (int i = 1; i <= keyWords.size() * numParameters; i += numParameters) {
			db.statement.setLong(i, getId());
			db.statement.setString(i + 1, keyWords.get(i / numParameters));
		}

		db.statement.execute();
	}

	public List<Work> loadWorks(String search, SearchSpecifications ss) throws Exception {
		/* @formatter:off */
		String selectWorks = "Select w.id, w.title, w.theme, w.category, w.downloads, w.description, a.first_name, a.last_name "
				+ " from works w inner join authors a ON (w.id_author = a.id) "
				+ " where 1=1 ";
		/* @formatter:on */
		switch (ss.getCategory()) {
		case Autor:
			selectWorks += " and ( a.first_name like ? || a.last_name like ? ) ";
			db.statement = WorkSharedDB.connection.prepareStatement(selectWorks);
			db.statement.setString(1, "%" + search + "%");
			db.statement.setString(2, "%" + search + "%");
			break;
		case Descrição:
			selectWorks += " and description like ? ";
			db.statement = WorkSharedDB.connection.prepareStatement(selectWorks);
			db.statement.setString(1, "%" + search + "%");
			break;
		case Tema:
			selectWorks += " and theme like ? ";
			db.statement = WorkSharedDB.connection.prepareStatement(selectWorks);
			db.statement.setString(1, "%" + search + "%");
			break;
		case Título:
			selectWorks += " and title like ? ";
			db.statement = WorkSharedDB.connection.prepareStatement(selectWorks);
			db.statement.setString(1, "%" + search + "%");
			break;
		case Categoria:
			selectWorks += " and category like ? ";
			db.statement = WorkSharedDB.connection.prepareStatement(selectWorks);
			db.statement.setString(1, "%" + search + "%");
			break;
		default:
			throw new Exception("Esta categoria de busca não existe");
		}

		try {
			ResultSet rs = db.statement.executeQuery();

			return parseToSimpleWorks(rs);
		} catch (SQLException e) {
			throw new Exception("Erro ao buscar trabalhos:" + e.getMessage());
		}
	}

	private List<Work> parseToSimpleWorks(ResultSet rs) throws SQLException, Exception {
		List<Work> simpleWorks = new ArrayList<Work>();
		while (rs.next()) {
			Work work = new Work();
			work.setId(rs.getLong("w.id"));
			work.setTitle(rs.getString("w.title"));
			work.setTheme(rs.getString("w.theme"));
			work.setCategory(WorkCategory.valueOf(rs.getString("w.category")));
			work.setDescription(rs.getString("w.description"));
			work.setNumberOfDownloads(rs.getLong("w.downloads"));
			work.setAuthorName(rs.getString("a.first_name") + " " + rs.getString("a.last_name"));
			simpleWorks.add(work);
		}

		return simpleWorks;
	}

	public void setCategory(WorkCategory category) throws Exception {
		doStringCheck(category.toString(), "Categoria", 50);
		this.category = category;
	}

	public void setDescription(String description) throws Exception {
		doStringCheck(description, "Descrição", 2000);
		this.description = description;
	}

	public void setFile(File file) {
		this.file = file;
	}

	public void setGrade(Grade grade) {
		this.grade = grade;
	}

	public void setId(long id) {
		this.id = id;
	}

	public void setKeyWords(List<String> keyWords) throws Exception {
		if (keyWords != null) {
			for (String key : keyWords) {
				if (key.trim().length() > 50) {
					throw new Exception("Cada palavra-chave pode conter até " + 50 + " caracteres!");
				}
			}
			this.keyWords = keyWords;
		} else {
			throw new Exception("As palavras-chave não podem ser nulas!");
		}
	}

	public void setNumberOfDownloads(long numberOfDownloads) {
		this.numberOfDownloads = numberOfDownloads;
	}

	public void setTheme(String theme) throws Exception {
		doStringCheck(theme, "Tema", 255);
		this.theme = theme;
	}

	public void setTitle(String title) throws Exception {
		doStringCheck(title, "Título", 255);
		this.title = title;
	}

	public String getKeyWordsString() {
		if (keyWords != null && !keyWords.isEmpty()) {
			StringBuilder keys = new StringBuilder(keyWords.get(0));
			for (int i = 0; i < keyWords.size(); i++) {
				keys.append(", " + keyWords.get(i));
			}
			return keys.toString();
		} else {
			return "";
		}
	}

	public String getAuthorName() {
		return authorName;
	}

	public void setAuthorName(String authorName) {
		this.authorName = authorName;
	}

	public void setAbsoluteFilePath(String path) {
		this.absoluteFilePath = path;
	}

	public String getAbsoluteFilePath() {
		return this.absoluteFilePath;
	}

}
